{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7c31ff8a",
   "metadata": {},
   "source": [
    "# 应用RFM模型对客户分群并进行探索性分析 \n",
    "##### __本文件输出为构建好的分群数据集rfm_df.csv，包含如下特征：'Customer ID', 'Recency', 'Frequency', 'Monetary', 'R_score', 'F_score','M_score', 'Customer Type'__\n",
    "##### __分群时段选择为最近的一个完整自然年（day=221），每年前12天的数据缺失且每年都只有221至222个销售日，望知晓。__\n",
    "- 各变量均采用均分（等频分箱）的方法构建分箱。\n",
    "- 对于各个变量的分箱个数均以3个为准。 \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14f54a76",
   "metadata": {},
   "source": [
    "#### 模块与数据准备 Model and Data Preparing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1a441a0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns \n",
    "from IPython.display import display\n",
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "74064435",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams[\"font.family\"] = [\"SimHei\", \"Arial\"]\n",
    "plt.rcParams['axes.unicode_minus'] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a8dcd6a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.set_style('whitegrid')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e1240843",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('../data/processed/cleaned_e_commerce_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fa33168b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Customer ID', 'Customer Name', 'Segment', 'City', 'State', 'Country',\n",
       "       'Region', 'Gender', 'Age', 'Education', 'Marital Status', 'Order ID',\n",
       "       'Order Date', 'Months', 'Ship Mode', 'Product Category', 'Product',\n",
       "       'Sales', 'Quantity', 'Discount', 'Order Priority',\n",
       "       'Browsing Time (min)', 'Like', 'Share', 'Add to Cart', 'Unit_price',\n",
       "       'Gross_Profit', 'COGS', 'Shipping_Cost_Reconstructed', 'Operating_Cost',\n",
       "       'Net_Profit', 'Net_Profit_Margin', 'Gross_Profit_Margin', 'age_kmeans',\n",
       "       'Year', 'Quarter', 'Season'],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(51207, 37)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df.columns, df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dea6db4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 51207 entries, 0 to 51206\n",
      "Data columns (total 37 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   Customer ID                  51207 non-null  object \n",
      " 1   Customer Name                51207 non-null  object \n",
      " 2   Segment                      51207 non-null  object \n",
      " 3   City                         51207 non-null  object \n",
      " 4   State                        51207 non-null  object \n",
      " 5   Country                      51207 non-null  object \n",
      " 6   Region                       51207 non-null  object \n",
      " 7   Gender                       51207 non-null  object \n",
      " 8   Age                          51207 non-null  int64  \n",
      " 9   Education                    51207 non-null  object \n",
      " 10  Marital Status               51207 non-null  object \n",
      " 11  Order ID                     51207 non-null  object \n",
      " 12  Order Date                   51207 non-null  object \n",
      " 13  Months                       51207 non-null  object \n",
      " 14  Ship Mode                    51207 non-null  object \n",
      " 15  Product Category             51207 non-null  object \n",
      " 16  Product                      51207 non-null  object \n",
      " 17  Sales                        51207 non-null  float64\n",
      " 18  Quantity                     51207 non-null  float64\n",
      " 19  Discount                     51207 non-null  float64\n",
      " 20  Order Priority               51207 non-null  object \n",
      " 21  Browsing Time (min)          51207 non-null  float64\n",
      " 22  Like                         51207 non-null  int64  \n",
      " 23  Share                        51207 non-null  int64  \n",
      " 24  Add to Cart                  51207 non-null  int64  \n",
      " 25  Unit_price                   51207 non-null  float64\n",
      " 26  Gross_Profit                 51207 non-null  float64\n",
      " 27  COGS                         51207 non-null  float64\n",
      " 28  Shipping_Cost_Reconstructed  51207 non-null  float64\n",
      " 29  Operating_Cost               51207 non-null  float64\n",
      " 30  Net_Profit                   51207 non-null  float64\n",
      " 31  Net_Profit_Margin            51207 non-null  float64\n",
      " 32  Gross_Profit_Margin          51207 non-null  float64\n",
      " 33  age_kmeans                   51207 non-null  object \n",
      " 34  Year                         51207 non-null  int64  \n",
      " 35  Quarter                      51207 non-null  object \n",
      " 36  Season                       51207 non-null  object \n",
      "dtypes: float64(12), int64(5), object(20)\n",
      "memory usage: 14.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a92e62f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Order Date'] = pd.to_datetime(df['Order Date'],errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad408ced",
   "metadata": {},
   "source": [
    "#### 构造模型数据集 Constructing model's dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e07f3543",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2024-01-13 00:00:00')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Order Date'] >= '2024-01-01']['Order Date'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6ef0d25b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2024 = df.loc[df['Order Date'] >= '2024-01-13'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "669ab06d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Canonical RFM computation and 1-3 scoring (uniform quantile bins)\n",
    "# Ensure datetime and define reference date as the max date within the window\n",
    "if not np.issubdtype(df_2024['Order Date'].dtype, np.datetime64):\n",
    "    df_2024['Order Date'] = pd.to_datetime(df_2024['Order Date'], errors='coerce')\n",
    "current_date = df_2024['Order Date'].max()\n",
    "\n",
    "# Aggregate to customer level\n",
    "rfm_df = (\n",
    "    df_2024\n",
    "    .groupby('Customer ID', as_index=False)\n",
    "    .agg(\n",
    "        Last_Purchase_Date=('Order Date', 'max'),\n",
    "        Frequency=('Order ID', 'nunique'),  # count unique orders\n",
    "        Monetary=('Sales', 'sum')           # total spend\n",
    "    )\n",
    ")\n",
    "\n",
    "# Compute Recency (days since last purchase within the window)\n",
    "rfm_df['Recency'] = (current_date - rfm_df['Last_Purchase_Date']).dt.days\n",
    "\n",
    "# Keep canonical columns order\n",
    "rfm_df = rfm_df[['Customer ID', 'Recency', 'Frequency', 'Monetary']]\n",
    "\n",
    "# Quantile-based uniform binning into 3 buckets with correct directions\n",
    "rfm_df['R_score'] = pd.qcut(rfm_df['Recency'], q=3, labels=[3, 2, 1], duplicates='drop').astype(int)\n",
    "rfm_df['F_score'] = pd.qcut(rfm_df['Frequency'], q=3, labels=[1, 2, 3], duplicates='drop').astype(int)\n",
    "rfm_df['M_score'] = pd.qcut(rfm_df['Monetary'], q=3, labels=[1, 2, 3], duplicates='drop').astype(int)\n",
    "\n",
    "# rfm_df now contains: Customer ID, Recency, Frequency, Monetary, R_score, F_score, M_score\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d91998a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Recency</th>\n",
       "      <th>Frequency</th>\n",
       "      <th>Monetary</th>\n",
       "      <th>R_score</th>\n",
       "      <th>F_score</th>\n",
       "      <th>M_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AB-00363</td>\n",
       "      <td>13</td>\n",
       "      <td>21</td>\n",
       "      <td>8921.24</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AB-00421</td>\n",
       "      <td>34</td>\n",
       "      <td>12</td>\n",
       "      <td>6203.39</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AB-004408</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>8836.38</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AH-00722</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>7443.16</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AK-00157</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>7479.15</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>YD-0038</td>\n",
       "      <td>2</td>\n",
       "      <td>19</td>\n",
       "      <td>7666.88</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>790</th>\n",
       "      <td>YE-00802</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>7437.04</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>YN-002590</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>9263.89</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>ZA-00198</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>20506.20</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>ZO-00115</td>\n",
       "      <td>6</td>\n",
       "      <td>27</td>\n",
       "      <td>11095.62</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>794 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Customer ID  Recency  Frequency  Monetary  R_score  F_score  M_score\n",
       "0      AB-00363       13         21   8921.24        2        2        2\n",
       "1      AB-00421       34         12   6203.39        1        1        1\n",
       "2     AB-004408        5         19   8836.38        3        1        2\n",
       "3      AH-00722       10         16   7443.16        2        1        1\n",
       "4      AK-00157        0         18   7479.15        3        1        1\n",
       "..          ...      ...        ...       ...      ...      ...      ...\n",
       "789     YD-0038        2         19   7666.88        3        1        1\n",
       "790    YE-00802        0         17   7437.04        3        1        1\n",
       "791   YN-002590       10         22   9263.89        2        2        2\n",
       "792    ZA-00198        1         41  20506.20        3        3        3\n",
       "793    ZO-00115        6         27  11095.62        3        3        3\n",
       "\n",
       "[794 rows x 7 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f80c33a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 794 entries, 0 to 793\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   Customer ID  794 non-null    object \n",
      " 1   Recency      794 non-null    int64  \n",
      " 2   Frequency    794 non-null    int64  \n",
      " 3   Monetary     794 non-null    float64\n",
      " 4   R_score      794 non-null    int32  \n",
      " 5   F_score      794 non-null    int32  \n",
      " 6   M_score      794 non-null    int32  \n",
      "dtypes: float64(1), int32(3), int64(2), object(1)\n",
      "memory usage: 34.2+ KB\n"
     ]
    }
   ],
   "source": [
    "rfm_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ee1e3100",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfm_df['R_score'] = rfm_df['R_score'].astype(int)\n",
    "rfm_df['F_score'] = rfm_df['F_score'].astype(int)\n",
    "rfm_df['M_score'] = rfm_df['M_score'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2950f1c1",
   "metadata": {},
   "source": [
    "#### 基于业务规则映射的经典八分群策略 Classic Octet Strategy Based on Business Rule Mapping\n",
    "- 由于样本量及对后续分析的影响，部分群体无人，部分群体人数过少而合并，故此，实际分为六群\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a51e49fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def rfm_segmentation_classic_8(df):\n",
    "    \"\"\"\n",
    "    经典8分群客户分群函数  仅优化了标签名称。\n",
    "\n",
    "    本函数严格遵循经典的RFM八群体分群策略,根据客户的最近购买行为(R)、\n",
    "    购买频率(F)和消费金额(M)三个维度的得分 1-3分, 3分最高, 将客户\n",
    "    划分为8个不同的价值群体。相较于原始版本, 本函数仅对返回的客户群体\n",
    "    名称进行了现代化和专业化的优化，使其更易于理解和指导后续的营销策略。\n",
    "\n",
    "    分群规则与标签如下：\n",
    "    1.  R=3, F=3, M=3 ->                                                                                '核心价值客户 (Champions)'\n",
    "    2.  R=3, F=3, M=2 ->                                                                                '高频忠诚客户 (Loyal High-Frequency)'\n",
    "    3.  R=3, F=2, M=3; R=3, F=2, M=2; R=3, F=2, M=1; R=3, F=1, M=3; R=3, F=3, M=1 ->                    '潜力客户 (Potential Loyalists)'\n",
    "    4.  R=3, F=1, M=2; R=3, F=1, M=1 ->                                                                 '新客户 (New Customers)'\n",
    "    5.  R=2 (所有F, M组合) ->                                                                            '摇摆客户 (On-the-Fence)'\n",
    "    6.  R=1, F=3, M=3; R=1, F=3, M=2; R=1, F=3, M=1; R=1, F=2, M=3; R=1, F=2, M=2; R=1, F=2, M=1 ->     '流失风险客户 (At Risk)'\n",
    "    7.  R=1, F=1, M=3 ->                                                                                '高价值流失客户 (Lost High-Value)'\n",
    "    8.  R=1, F=1, M=2; R=1, F=1, M=1 ->                                                                 '沉睡客户 (Inactive)'\n",
    "\n",
    "    参数:\n",
    "    df (pandas.Series): 包含单个客户RFM得分的Pandas Series，必须包含\n",
    "                        'R_score', 'F_score', 'M_score'三个键。通常通过\n",
    "                        DataFrame.apply()方法逐行传入。\n",
    "\n",
    "    返回:\n",
    "    str: 客户所属的分群标签（字符串）。\n",
    "    \"\"\"\n",
    "    r, f, m = df['R_score'], df['F_score'], df['M_score']\n",
    "\n",
    "    if r == 3 and f == 3 and m == 3:\n",
    "        return 'Champions'\n",
    "    \n",
    "    elif r == 3 and f == 3 and m == 2:\n",
    "        return 'Loyal High-Frequency'\n",
    "    \n",
    "    elif (r == 3 and f == 2 and m == 3) or (r == 3 and f == 2 and m == 2) or (r == 3 and f == 2 and m == 1) or \\\n",
    "         (r == 3 and f == 1 and m == 3) or (r == 3 and f == 3 and m == 1):\n",
    "        return 'Potential Loyalists'\n",
    "    \n",
    "    elif (r == 3 and f == 1 and m == 2) or (r == 3 and f == 1 and m == 1):\n",
    "        return 'New Customers'\n",
    "    \n",
    "    elif r == 2: # 涵盖所有 R=2 的组合\n",
    "        return 'On-the-Fence'\n",
    "    \n",
    "    elif (r == 1 and f == 3 and m == 3) or (r == 1 and f == 3 and m == 2) or (r == 1 and f == 3 and m == 1) or \\\n",
    "         (r == 1 and f == 2 and m == 3) or (r == 1 and f == 2 and m == 2) or (r == 1 and f == 2 and m == 1):\n",
    "        return 'At Risk'\n",
    "    \n",
    "    elif r == 1 and f == 1 and m == 3:\n",
    "        return 'Lost High-Value'\n",
    "    \n",
    "    else: # r == 1, f == 1 and (m == 1 or m == 2)\n",
    "        return 'Inactive'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e5d014d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfm_df['Customer Type'] = rfm_df.apply(rfm_segmentation_classic_8, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "08eae76a",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfm_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e30d7cfa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customer Type\n",
      "On-the-Fence            371\n",
      "New Customers            95\n",
      "Potential Loyalists      84\n",
      "Inactive                 81\n",
      "Champions                80\n",
      "At Risk                  62\n",
      "Loyal High-Frequency     19\n",
      "Lost High-Value           2\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 先对 DataFrame 按照 Customer ID 进行去重，保留每个 Customer ID 的第一条记录\n",
    "unique_customers = rfm_df.drop_duplicates(subset='Customer ID')\n",
    "# 然后统计去重后 Customer Type 的数量\n",
    "customer_type_count = unique_customers['Customer Type'].value_counts()\n",
    "print(customer_type_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4c995fe7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_and_rfm_df = pd.merge(left=df,right=rfm_df,on='Customer ID',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6569dccd",
   "metadata": {},
   "outputs": [],
   "source": [
    "lost_high_value_customer = df_and_rfm_df[df_and_rfm_df['Customer Type'] == 'Lost High-Value'][['Customer ID','Sales']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b090fdb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Customer ID\n",
       "EN-00315     30233.69\n",
       "ON-001736    28237.87\n",
       "Name: Sales, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lost_high_value_customer.groupby('Customer ID')['Sales'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "91601118",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lost High-Value 客户并非历史销售额极高人群，并且样本量小，合并到At Risk\n",
    "# Loyal High-Frequency因为高频忠诚客户与高价值客户（Champions）客户的差异为消费额贡献差异，为了后续分析，将其归为高价值客户Champions\n",
    "rfm_df.loc[rfm_df['Customer Type'].eq('Loyal High-Frequency'), 'Customer Type'] = 'Champions'\n",
    "rfm_df.loc[rfm_df['Customer Type'].eq('Lost High-Value'),'Customer Type'] = 'At Risk'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "18df6a3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customer Type\n",
      "On-the-Fence           371\n",
      "Champions               99\n",
      "New Customers           95\n",
      "Potential Loyalists     84\n",
      "Inactive                81\n",
      "At Risk                 64\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 先对 DataFrame 按照 Customer ID 进行去重，保留每个 Customer ID 的第一条记录\n",
    "unique_customers = rfm_df.drop_duplicates(subset='Customer ID')\n",
    "# 然后统计去重后 Customer Type 的数量\n",
    "customer_type_count = unique_customers['Customer Type'].value_counts()\n",
    "print(customer_type_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "25896709",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Recency</th>\n",
       "      <th>Frequency</th>\n",
       "      <th>Monetary</th>\n",
       "      <th>R_score</th>\n",
       "      <th>F_score</th>\n",
       "      <th>M_score</th>\n",
       "      <th>Customer Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>AB-00363</td>\n",
       "      <td>13</td>\n",
       "      <td>21</td>\n",
       "      <td>8921.24</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>On-the-Fence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>AB-00421</td>\n",
       "      <td>34</td>\n",
       "      <td>12</td>\n",
       "      <td>6203.39</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>AB-004408</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>8836.38</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>New Customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>AH-00722</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>7443.16</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>On-the-Fence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>AK-00157</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>7479.15</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>New Customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>789</td>\n",
       "      <td>YD-0038</td>\n",
       "      <td>2</td>\n",
       "      <td>19</td>\n",
       "      <td>7666.88</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>New Customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>790</th>\n",
       "      <td>790</td>\n",
       "      <td>YE-00802</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>7437.04</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>New Customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>791</td>\n",
       "      <td>YN-002590</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>9263.89</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>On-the-Fence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>792</td>\n",
       "      <td>ZA-00198</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>20506.20</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>793</td>\n",
       "      <td>ZO-00115</td>\n",
       "      <td>6</td>\n",
       "      <td>27</td>\n",
       "      <td>11095.62</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>794 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index Customer ID  Recency  Frequency  Monetary  R_score  F_score  \\\n",
       "0        0    AB-00363       13         21   8921.24        2        2   \n",
       "1        1    AB-00421       34         12   6203.39        1        1   \n",
       "2        2   AB-004408        5         19   8836.38        3        1   \n",
       "3        3    AH-00722       10         16   7443.16        2        1   \n",
       "4        4    AK-00157        0         18   7479.15        3        1   \n",
       "..     ...         ...      ...        ...       ...      ...      ...   \n",
       "789    789     YD-0038        2         19   7666.88        3        1   \n",
       "790    790    YE-00802        0         17   7437.04        3        1   \n",
       "791    791   YN-002590       10         22   9263.89        2        2   \n",
       "792    792    ZA-00198        1         41  20506.20        3        3   \n",
       "793    793    ZO-00115        6         27  11095.62        3        3   \n",
       "\n",
       "     M_score  Customer Type  \n",
       "0          2   On-the-Fence  \n",
       "1          1       Inactive  \n",
       "2          2  New Customers  \n",
       "3          1   On-the-Fence  \n",
       "4          1  New Customers  \n",
       "..       ...            ...  \n",
       "789        1  New Customers  \n",
       "790        1  New Customers  \n",
       "791        2   On-the-Fence  \n",
       "792        3      Champions  \n",
       "793        3      Champions  \n",
       "\n",
       "[794 rows x 9 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3f0b434d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'Customer ID', 'Recency', 'Frequency', 'Monetary', 'R_score',\n",
       "       'F_score', 'M_score', 'Customer Type'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "79e82e50",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfm_df.drop(columns=['index'],inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18af1901",
   "metadata": {},
   "source": [
    "#### 保存文件 Save file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ca225dff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save file\n",
    "rfm_df.to_csv('../data/processed/rfm_data.csv',encoding='utf-8', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "data_ana_project_env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
